﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Reflection;

namespace Mind.Data
{
    public abstract class Wrapper<TWrapper, TSchema> : IWrapper, IDataReadable
        where TSchema : Schema<TSchema>, new()
        where TWrapper : Wrapper<TWrapper, TSchema>, new()
    {
        private static Dictionary<string, string> _BasicCommandCache;
        private static Dictionary<string, string> BasicCommandCache
        {
            get
            {
                if (_BasicCommandCache == null)
                    _BasicCommandCache = new Dictionary<string, string>();
                return _BasicCommandCache;
            }
        }
        private static Schema<TSchema> _Schema;
        public static Schema<TSchema> Schema
        {
            get
            {
                if (_Schema == null)
                    _Schema = new TSchema();

                return _Schema;
            }
        }

        [NonSerialized]
        private Dictionary<string, object> _ColumnValues;
        [NonSerialized]
        private Dictionary<string, IWrapper> _LockupValues;
        [NonSerialized]
        private List<WrapperMessage> _ValidationMessages;

        public List<DataAccessParameter> MakeParameters(bool generateKey, bool noParamName)
        {
            List<DataAccessParameter> result = new List<DataAccessParameter>();

            foreach (ColumnInfo item in Schema.Columns)
            {
                if (generateKey || !item.IsKey)
                {
                    DataAccessParameter param = item.MakeParameter(_ColumnValues[item.ColumnName]);
                    if (noParamName)
                        param.Name = String.Empty;
                    result.Add(param);
                }
            }

            return result;
        }

        public DataAccessParameter MakeKeyParameter()
        {
            return Schema.KeyColumn.MakeParameter(this.GetKeyValue());
        }

        public Wrapper()
        {
            _ColumnValues = new Dictionary<string, object>();
            _LockupValues = new Dictionary<string, IWrapper>();
            _ValidationMessages = new List<WrapperMessage>();
            IsNew = true;
        }



        protected T? GetColumnValue<T>(string column) where T : struct
        {
            if (HasColumnValue(column))
            {
                T? result = _ColumnValues[column] as T?;
                return result;
            }
            else
            {
                return null;
            }
        }

        protected string GetColumnString(string column)
        {
            object result = null;
            if (HasColumnValue(column) && (result = _ColumnValues[column]) != null)
            {
                return result.ToString();
            }
            else
                return null;
        }

        public object GetColumnValue(string column)
        {
            if (HasColumnValue(column))
                return _ColumnValues[column];
            else
                return null;
        }

        public bool HasColumnValue(string column)
        {
            return _ColumnValues.ContainsKey(column);
        }

        public void SetColumnValue(string column, object value)
        {
            if (value is DBNull)
            {
                value = null;
            }
            else
            {
                if (value != null && !Schema[column].PropertyType.IsInstanceOfType(value))
                {
                    try
                    {
                        value = Convert.ChangeType(value, Schema[column].PropertyType);
                    }
                    catch
                    {
                        throw new NotSupportedException(String.Format("{0}.SetKeyValue error: The value '{1}' is not of type '{2}'.", typeof(TWrapper).Name, value, Schema.KeyColumn.PropertyType.Name));
                    }
                }
            }

            _ColumnValues[column] = value;
        }

        public void SetLockupValue(string lockup, IWrapper value)
        {
            if (value == null)
            {
                _ColumnValues[lockup] = null;
                _LockupValues[lockup] = null;
            }
            else
            {
                _ColumnValues[lockup] = value.GetKeyValue();
                _LockupValues[lockup] = value;
            }
        }

        IWrapper IWrapper.GetLockupValue(string lockup)
        {
            ColumnInfo col = Schema[lockup];
            LockupInfo info = col.LockupInfo;
            if (info == null) return null;

            MethodInfo method = this.GetType().GetMethod("GetLockupValue");

            if (method.IsGenericMethod)
                return method.MakeGenericMethod(info.WrapperType).Invoke(this, new object[] { lockup }) as IWrapper;

            return null;
        }

        public TLockupWrapper GetLockupValue<TLockupWrapper>(string lockup) where TLockupWrapper : class, IWrapper, new()
        {
            TLockupWrapper result = null;
            ColumnInfo col = Schema[lockup];
            LockupInfo info = col.LockupInfo;
            if (info == null) return null;

            object fkValue = GetColumnValue(col.ColumnName);

            switch (info.LockupType)
            {
                case LockupType.FullCached:
                    result = WrapperCache.GetById<TLockupWrapper>(fkValue, true);
                    break;
                case LockupType.Cached:
                    result = WrapperCache.GetById<TLockupWrapper>(fkValue, false);
                    break;
                case LockupType.LazyCached:
                    if (!_LockupValues.ContainsKey(lockup))
                    {
                        result = new TLockupWrapper();
                        result.SetKeyValue(fkValue);
                        if (!result.GetById())
                            result = null;
                    }
                    else
                        result = _LockupValues[lockup] as TLockupWrapper;
                    break;
                case LockupType.Joinned:
                    if (_LockupValues.ContainsKey(lockup))
                    {
                        result = _LockupValues[lockup] as TLockupWrapper;
                        break;
                    }
                    goto default;
                case LockupType.Lazy:
                default:
                    result = new TLockupWrapper();
                    result.SetKeyValue(fkValue);
                    if (!result.GetById())
                        result = null;
                    break;
            }

            return result;

        }

        public object GetKeyValue()
        {
            return _ColumnValues[Schema.KeyColumn.ColumnName];
        }

        public void SetKeyValue(object value)
        {
            SetColumnValue(Schema.KeyColumn.ColumnName, value);
        }

        /// <summary>
        /// This is a special property that controls the Save() behavior.
        /// You can set it to change the Save ways, true generates an INSERT and false generates an UPDATE.
        /// To Delete() function this property does not matter.
        /// 
        /// This property is automatic changed when:
        /// - Insert occurs (Set to false)
        /// - Delete occurs (Set to true)
        /// - Select like methods (set to false)
        /// - Convert methods (such a DataRow To Wrapper) (set to false)
        /// </summary>
        public virtual bool IsNew
        {
            get;
            set;
        }

        public WrapperMessage[] ValidationMessages
        {
            get
            {
                return _ValidationMessages.ToArray();
            }
        }

        public void ReInsert()
        {
            IsNew = true;
            Save(true);
        }

        public void Save()
        {
            Save(false);
        }

        public bool IsValid
        {
            get
            {
                return Validate(_ValidationMessages);
            }
        }

        protected virtual bool Validate(List<WrapperMessage> messages)
        {
            return true;
        }

        public virtual void Save(bool disableAutoIncrement)
        {
            if (!IsValid)
                throw new WrapperInvalidException(ValidationMessages);

            if (IsNew)
            {
                Insert(disableAutoIncrement);
            }
            else
            {
                Update();
            }

        }

        

        public virtual int Delete()
        {
            const string DELETE_KEY = "DELETE";
            String sql = GetBasicCommandCache(DELETE_KEY);
            DataAccessParameter param = MakeKeyParameter();

            if (String.IsNullOrEmpty(sql))
            {
                StringBuilder command = new StringBuilder();
                command.AppendFormat("DELETE FROM {0} ", Schema.TableName);
                command.AppendLine();
                command.AppendFormat("WHERE {0} = @{0}", param.Name);
                sql = command.ToString();
                BasicCommandCache.Add(DELETE_KEY, sql);
            }
            IsNew = true;
            return DataAccessManager.Current.ExecuteNonQuery(sql, param);
        }

        protected virtual int Update()
        {
            const string UPDATE_KEY = "UPDATE";
            string sql = GetBasicCommandCache(UPDATE_KEY);
            List<DataAccessParameter> parameters = MakeParameters(false, false);
            DataAccessParameter keyParam = MakeKeyParameter();

            parameters.Add(keyParam);

            if (String.IsNullOrEmpty(sql))
            {
                StringBuilder command = new StringBuilder();
                command.AppendFormat("UPDATE {0} SET", Schema.TableName);
                command.AppendLine();
                bool first = true;

                foreach (DataAccessParameter param in parameters)
                {
                    if (Schema[param.Name].IsKey)
                        continue;

                    if (!first)
                        command.Append(", ");
                    else
                        first = false;

                    command.AppendFormat("{0} = @{0}", param.Name);
                    command.AppendLine();
                }

                command.AppendFormat("WHERE {0} = @{0}", keyParam.Name);
                sql = command.ToString();
                BasicCommandCache.Add(UPDATE_KEY, sql);
            }

            return DataAccessManager.Current.ExecuteNonQuery(sql, parameters.ToArray());
        }

        protected virtual void Insert(bool skipAutoIncrement)
        {
            const string INSERT_KEY = "INSERT";
            String sql = GetBasicCommandCache(INSERT_KEY);
            List<DataAccessParameter> parameters = MakeParameters(skipAutoIncrement || !Schema.KeyColumn.AutoIncrement, false);

            if (String.IsNullOrEmpty(sql))
            {
                StringBuilder command = new StringBuilder();
                command.AppendFormat("INSERT INTO {0} ", Schema.TableName);
                command.AppendLine();
                command.Append("(");
                foreach (DataAccessParameter param in parameters)
                {
                    if (param != parameters[0])
                        command.Append(", ");

                    command.Append(param.Name);
                }
                command.AppendLine(")");
                command.Append("VALUES (");

                foreach (DataAccessParameter param in parameters)
                {
                    if (param != parameters[0])
                        command.Append(", ");

                    command.AppendFormat("@{0}", param.Name);
                }
                command.AppendLine(")");
                
                if (Schema.KeyColumn.AutoIncrement)
                    command.AppendLine("SELECT @@IDENTITY");

                sql = command.ToString();

                BasicCommandCache.Add(INSERT_KEY, sql);
            }

            if (Schema.KeyColumn.AutoIncrement && !skipAutoIncrement)
            {
                SetKeyValue(DataAccessManager.Current.ExecuteScalar(sql, parameters.ToArray()));
            }
            else
            {
                DataAccessManager.Current.ExecuteNonQuery(sql, parameters.ToArray());
            }

            IsNew = false;
        }

        public override string ToString()
        {
            StringBuilder descricao = new StringBuilder();
            bool first = true;
            foreach (ColumnInfo ci in Schema.NameColumns)
            {
                if (first)
                    first = false;
                else
                    descricao.Append(", ");

                if (ci.LockupInfo != null)
                    descricao.Append(((IWrapper)this).GetLockupValue(ci.ColumnName));
                else
                    descricao.Append(GetColumnValue(ci.ColumnName));
            }

            return descricao.ToString();
        }


        public bool LoadFromDataReader(DbDataReader reader)
        {
            Dictionary<string, int> readerColumns = new Dictionary<string, int>();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                string col = reader.GetName(i);
                if (!readerColumns.ContainsKey(col))
                    readerColumns.Add(col, i);
            }
            return LoadFromDataReader(this, reader, null, readerColumns);
        }

        private static bool LoadFromDataReader(IWrapper wrapper, DbDataReader reader, LockupInfo lockup, Dictionary<string, int> readerColumns)
        {
            if (reader.IsClosed)
                return false;


            foreach (ColumnInfo colInfo in wrapper.GetSchema().Columns)
            {
                string colAlias = lockup == null ? colInfo.UniqueAlias : lockup.GenerateUniqueAlias(colInfo);
                if (readerColumns.ContainsKey(colAlias))
                {
                    wrapper.SetColumnValue(colInfo.ColumnName, reader.GetValue(readerColumns[colAlias]));

                    if (colInfo.LockupInfo != null
                    && readerColumns.ContainsKey(colInfo.LockupInfo.GenerateUniqueAlias(colInfo.LockupInfo.ForeingSchema.KeyColumn)))
                    {
                        IWrapper lockupInstance = colInfo.LockupInfo.MakeLockupInstance();
                        if (LoadFromDataReader(lockupInstance, reader, colInfo.LockupInfo, readerColumns))
                        {
                            wrapper.SetLockupValue(colInfo.ColumnName, lockupInstance);
                        }
                    }
                }
                else if (readerColumns.ContainsKey(colInfo.ColumnName))
                {
                    wrapper.SetColumnValue(colInfo.ColumnName, reader.GetValue(readerColumns[colInfo.ColumnName]));
                }
            }

            wrapper.IsNew = false;
            return true;
        }



        public void LoadFromDataRow(DataRow row)
        {
            foreach (ColumnInfo colInfo in Schema.Columns)
            {
                if (row.Table.Columns.Contains(colInfo.ColumnName))
                {
                    SetColumnValue(colInfo.ColumnName, row[row.Table.Columns[colInfo.ColumnName]]);
                }
            }

            IsNew = false;
        }

        private static string GetBasicCommandCache(string key)
        {
            if (BasicCommandCache.ContainsKey(key))
                return BasicCommandCache[key];
            else
                return null;
        }

        private static string GenerateBasicSelect(int? top)
        {
            const string SELECT_KEY = "SELECT";
            String sql = GetBasicCommandCache(SELECT_KEY);

            if (String.IsNullOrEmpty(sql))
            {
                List<ColumnInfo> joinColumn = new List<ColumnInfo>();
                StringBuilder command = new StringBuilder();
                command.AppendLine("SELECT {0}");
                bool first = true;
                foreach (ColumnInfo colInfo in Schema.Columns)
                {
                    if (!first) command.Append(", "); else first = false;
                    command.AppendFormat("{0}.{1} AS {2}", Schema.TableName, colInfo.ColumnName, colInfo.UniqueAlias);

                    if (colInfo.LockupInfo != null && colInfo.LockupInfo.LockupType == LockupType.Joinned)
                    {
                        joinColumn.Add(colInfo);

                        foreach (ColumnInfo lockupColumns in colInfo.LockupInfo.ForeingSchema.Columns)
                        {
                            command.AppendFormat(", tbl_{0}.{1} AS {2}"
                                , colInfo.ColumnName
                                , lockupColumns.ColumnName
                                , colInfo.LockupInfo.GenerateUniqueAlias(lockupColumns));
                        }


                    }
                }
                command.AppendLine();
                command.AppendFormat("FROM {0}", Schema.TableName);

                foreach (ColumnInfo join in joinColumn)
                {
                    command.AppendLine();
                    command.AppendFormat("LEFT OUTER JOIN {0} tbl_{1} ON tbl_{1}.{2} = {3}.{1}",
                        join.LockupInfo.ForeingSchema.TableName, join.ColumnName, join.LockupInfo.ForeingSchema.KeyColumn.ColumnName, join.Schema.TableName);
                }

                command.AppendLine();

                sql = command.ToString();
                BasicCommandCache.Add(SELECT_KEY, sql);
            }

            return String.Format(sql, top.HasValue ? String.Format("TOP {0}", top.Value) : String.Empty);
        }
        public static TWrapper FindFirst()
        {
            return FindFirst(String.Empty, String.Empty);
        }

        public static TWrapper FindFirst(string where, params IDataAccessParameter[] parameters)
        {
            return FindFirst(where, String.Empty, parameters);
        }

        public static TWrapper FindFirst(string where, string orderBy, params IDataAccessParameter[] parameters)
        {
            TWrapper result = null;
            using (DataReaderIterator<TWrapper> iterator = FindTop(1, where, orderBy, parameters))
            {
                IEnumerator<TWrapper> enumerator = iterator.GetEnumerator();
                if (enumerator.MoveNext())
                    result = enumerator.Current;
            }
            return result;
        }

        public static TWrapper FormatFindFirst(string where, string orderBy, params object[] parameters)
        {
            TWrapper result = null;
            using (DataReaderIterator<TWrapper> iterator = FormatFindTop(1, where, orderBy, parameters))
            {
                IEnumerator<TWrapper> enumerator = iterator.GetEnumerator();
                if (enumerator.MoveNext())
                    result = enumerator.Current;
            }
            return result;
        }

        
        /// <summary>
        /// Retorna o único registro possível desta tabela.
        /// - Se não exisir, retorna NULL
        /// - Se existir mais de um, dá uma exception.
        /// 
        /// Internamente, retorna 2 registros usando DataReader. Se veio dois registros, lança aexceção.
        /// </summary>
        /// <returns>O registro</returns>
        public static TWrapper FindOne()
        {
            return FindOne(String.Empty, String.Empty);
        }

        /// <summary>
        /// Retorna o único registro possível desta tabela.
        /// - Se não exisir, retorna NULL
        /// - Se existir mais de um, dá uma exception.
        /// 
        /// Internamente, retorna 2 registros usando DataReader. Se veio dois registros, lança aexceção.
        /// </summary>
        /// <returns>O registro</returns>
        public static TWrapper FindOne(string where, params IDataAccessParameter[] parameters)
        {
            return FindOne(where, String.Empty, parameters);
        }

        /// <summary>
        /// Retorna o único registro possível desta tabela.
        /// - Se não exisir, retorna NULL
        /// - Se existir mais de um, dá uma exception.
        /// 
        /// Internamente, retorna 2 registros usando DataReader. Se veio dois registros, lança aexceção.
        /// </summary>
        /// <returns>O registro</returns>
        public static TWrapper FindOne(string where, string orderBy, params IDataAccessParameter[] parameters)
        {
            using (DataReaderIterator<TWrapper> iterator = FindTop(2, where, orderBy, parameters))
            {
                IEnumerator<TWrapper> enumerator = iterator.GetEnumerator();
                TWrapper result = null;
                if (enumerator.MoveNext())
                    result = enumerator.Current;

                if (!enumerator.MoveNext())
                    return result;
                else
                    throw new NotSupportedException(String.Format("{0}.FindOne error: There are two or more rows where '{1}'.", typeof(TWrapper).Name, where));

            }
        }


        /// <summary>
        /// Retorna o único registro possível desta tabela.
        /// - Se não exisir, retorna NULL
        /// - Se existir mais de um, dá uma exception.
        /// 
        /// Internamente, retorna 2 registros usando DataReader. Se veio dois registros, lança aexceção.
        /// </summary>
        /// <returns>O registro</returns>
        public static TWrapper FormatFindOne(string where, string orderBy, params object[] parameters)
        {
            using (DataReaderIterator<TWrapper> iterator = FormatFindTop(2, where, orderBy, parameters))
            {
                IEnumerator<TWrapper> enumerator = iterator.GetEnumerator();
                TWrapper result = null;
                if (enumerator.MoveNext())
                    result = enumerator.Current;

                if (!enumerator.MoveNext())
                    return result;
                else
                    throw new NotSupportedException(String.Format("{0}.FindOne error: There are two or more rows where '{1}'.", typeof(TWrapper).Name, where));

            }
        }


        public static TWrapper FindById(object id)
        {
            DataAccessParameter keyParameter = Schema.KeyColumn.MakeParameter(id);
            using (DataReaderIterator<TWrapper> iterator = FindTop(2,
                String.Format("{0}.{1} = @{1}"
                , Schema.TableName
                , Schema.KeyColumn.ColumnName), keyParameter))
            {
                IEnumerator<TWrapper> enumerator = iterator.GetEnumerator();
                TWrapper result = null;

                if (enumerator.MoveNext())
                {
                    result = enumerator.Current;

                    if (!enumerator.MoveNext())
                        return result;
                    else
                        throw new NotSupportedException(String.Format("{0}.FindById error: There are two or more rows with {1} = {2}", typeof(TWrapper).Name, keyParameter.Name, keyParameter.Value));
                }
                else
                    return null;
            }
        }

        public static DataReaderIterator<TWrapper> FindByColumn(string columnName, object value)
        {
            return FindByColumn(columnName, value, null);
        }

        public static DataReaderIterator<TWrapper> FindByColumn(string columnName, object value, string orderby)
        {
            ColumnInfo col = Schema[columnName];
            if (col == null) return null;
            DataAccessParameter columnParameter = col.MakeParameter(value);

            return FindAll(
                String.Format("{0}.{1} = @{1}"
                , Schema.TableName
                , col.ColumnName), orderby, columnParameter);
        }

        public static int Count()
        {
            return Count("*", String.Empty);
        }

        public static int Count(string where, params DbParameter[] parameters)
        {
            return Count("*", where, parameters);
        }

        public static int Count(string countColumn, string where, params DbParameter[] parameters)
        {
            if (String.IsNullOrEmpty(countColumn))
                countColumn = "*";
            StringBuilder command = new StringBuilder();
            command.AppendFormat("SELECT COUNT({0}) FROM {1} ", countColumn, Schema.TableName);
            command.AppendLine();

            if (!String.IsNullOrEmpty(where))
            {
                command.AppendLine("WHERE");
                command.AppendLine(where);
                command.AppendLine();
            }


            //Execute Scalar

            return DataAccessManager.Current.ExecuteInlineScalar<int>(command.ToString(), parameters);
        }

        public static bool Exists()
        {
            return Exists(String.Empty);
        }

        public static bool Exists(string where, params DataAccessParameter[] parameters)
        {
            using (DataReaderIterator<TWrapper> reader = FindTop(1, where, null, parameters))
            {
                if (reader.GetEnumerator().MoveNext())
                    return true;
                else
                    return false;
            }

        }

        public static DataReaderIterator<TWrapper> FindBySample(TWrapper wrapper)
        {
            ISchema schema = ((IWrapper)wrapper).GetSchema();
            StringBuilder where = new StringBuilder();
            bool first = true;

            where.AppendLine();

            List<DataAccessParameter> parameters = new List<DataAccessParameter>();
            foreach (ColumnInfo item in (schema.Columns))
            {
                if (item.IsKey || !wrapper.HasColumnValue(item.ColumnName)) continue;

                DataAccessParameter parameter = item.MakeParameter(wrapper.GetColumnValue(item.ColumnName));
                parameters.Add(parameter);

                if (first) where.Append("AND "); else first = false;

                where.AppendFormat("{0}.{1} {2} @{3}",
                        schema.TableName, item.ColumnName,
                        (item.PropertyType == typeof(string)) ? "LIKE" : "=",
                        parameter.Name);

                where.AppendLine();
            }

            if (parameters.Count == 0) where.Length = 0;

            return FindAll(where.ToString(), parameters.ToArray());

        }

        public static DataReaderIterator<TWrapper> FindAll()
        {
            return FindAll(String.Empty, String.Empty);
        }

        public static DataReaderIterator<TWrapper> FindAll(string where, params IDataAccessParameter[] parameters)
        {
            return FindAll(where, string.Empty, parameters);
        }

        public static DataReaderIterator<TWrapper> FindAll(string where, string orderBy, params IDataAccessParameter[] parameters)
        {
            StringBuilder command = new StringBuilder(GenerateBasicSelect(null));

            if (String.IsNullOrEmpty(orderBy))
                orderBy = GenerateDefaultOrderBy(Schema);

            if (!String.IsNullOrEmpty(where))
            {
                command.AppendLine("WHERE");
                command.AppendLine(where);
                command.AppendLine();
            }

            if (!String.IsNullOrEmpty(orderBy))
            {
                command.AppendLine("ORDER BY");
                command.AppendLine(orderBy);
            }


            DbDataReader reader = DataAccessManager.Current.ExecuteReader(command.ToString(), parameters);

            return new DataReaderIterator<TWrapper>(reader);

        }

        public static DataReaderIterator<TWrapper> FormatFindAll(string where, string orderBy, params object[] parameters)
        {
            StringBuilder command = new StringBuilder(GenerateBasicSelect(null));

            if (String.IsNullOrEmpty(orderBy))
                orderBy = GenerateDefaultOrderBy(Schema);

            if (!String.IsNullOrEmpty(where))
            {
                command.AppendLine("WHERE");
                command.AppendLine(where);
                command.AppendLine();
            }

            if (!String.IsNullOrEmpty(orderBy))
            {
                command.AppendLine("ORDER BY");
                command.AppendLine(orderBy);
            }


            DbDataReader reader = DataAccessManager.Current.ExecuteFormatReader(command.ToString(), parameters);

            return new DataReaderIterator<TWrapper>(reader);

        }

        public static DataReaderIterator<TWrapper> FindTop(int top)
        {
            return FindTop(top, String.Empty, String.Empty);
        }

        public static DataReaderIterator<TWrapper> FindTop(int top, string where, params IDataAccessParameter[] parameters)
        {
            return FindTop(top, where, String.Empty, parameters);
        }

        public static DataReaderIterator<TWrapper> FindTop(int top, string where, string orderBy, params IDataAccessParameter[] parameters)
        {
            StringBuilder command = new StringBuilder(GenerateBasicSelect(top));

            if (String.IsNullOrEmpty(orderBy))
                orderBy = GenerateDefaultOrderBy(Schema);

            if (!String.IsNullOrEmpty(where))
            {
                command.AppendLine("WHERE");
                command.AppendLine(where);
            }

            if (!String.IsNullOrEmpty(orderBy))
            {
                command.AppendLine("ORDER BY");
                command.AppendLine(orderBy);
            }


            DbDataReader reader = DataAccessManager.Current.ExecuteReader(command.ToString(), parameters);

            return new DataReaderIterator<TWrapper>(reader);

        }

        public static DataReaderIterator<TWrapper> FormatFindTop(int top, string where, string orderBy, params object[] parameters)
        {
            StringBuilder command = new StringBuilder(GenerateBasicSelect(top));

            if (String.IsNullOrEmpty(orderBy))
                orderBy = GenerateDefaultOrderBy(Schema);

            if (!String.IsNullOrEmpty(where))
            {
                command.AppendLine("WHERE");
                command.AppendLine(where);
            }

            if (!String.IsNullOrEmpty(orderBy))
            {
                command.AppendLine("ORDER BY");
                command.AppendLine(orderBy);
            }


            DbDataReader reader = DataAccessManager.Current.ExecuteFormatReader(command.ToString(), parameters);

            return new DataReaderIterator<TWrapper>(reader);

        }

        private static string GenerateDefaultOrderBy(ISchema schema)
        {
            return GenerateDefaultOrderBy(schema, null);
        }

        private static string GenerateDefaultOrderBy(ISchema schema, LockupInfo lockup)
        {
            const string ORDER_BY_KEY = "ORDER BY";
            String orderby = GetBasicCommandCache(ORDER_BY_KEY);
            if (string.IsNullOrEmpty(orderby))
            {
                StringBuilder result = new StringBuilder();
                bool first = true;
                foreach (ColumnInfo item in schema.NameColumns)
                {
                    if (first)
                        first = false;
                    else
                        result.Append(", ");

                    if (item.LockupInfo != null && item.LockupInfo.LockupType == LockupType.Joinned)
                    {
                        string lockupOrderBy = GenerateDefaultOrderBy(item.LockupInfo.ForeingSchema, item.LockupInfo);
                        if (!string.IsNullOrEmpty(lockupOrderBy))
                        {
                            result.AppendFormat(lockupOrderBy);
                            continue;
                        }
                    }

                    if (lockup == null)
                        result.Append(item.UniqueAlias);
                    else
                        result.AppendFormat("tbl_{0}.{1}"
                                    , lockup.Column.ColumnName
                                    , item.ColumnName);
                }

                orderby = result.ToString();
                BasicCommandCache[ORDER_BY_KEY] = orderby;
            }
            return orderby;
        }

        /// <summary>
        /// Implementado explicitamente para ficar oculto.
        /// </summary>
        /// <returns></returns>
        ISchema IWrapper.GetSchema()
        {
            return Schema;
        }

        /// <summary>
        /// Implementado explicitamente para ficar oculto.
        /// </summary>
        /// <returns></returns>
        bool IWrapper.GetById()
        {
            DataAccessParameter keyParameter = Schema.KeyColumn.MakeParameter(GetKeyValue());
            using (DataReaderIterator<TWrapper> iterator = FindTop(2,
                String.Format("{0}.{1} = @{2}"
                , Schema.TableName
                , Schema.KeyColumn.ColumnName), keyParameter))
            {
                DbDataReader reader = iterator.Reader;

                if (reader.Read())
                {
                    if (LoadFromDataReader(reader))
                    {
                        if (!reader.Read())
                            return true;
                        else
                            throw new NotSupportedException(String.Format("{0}.GetById error: There are two or more rows with {1} = {2}", typeof(TWrapper).Name, keyParameter.Name, keyParameter.Value));
                    }
                }

                return false;
            }
        }

        /// <summary>
        /// Implementado explicitamente para ficar oculto.
        /// </summary>
        /// <returns></returns>
        IWrapper[] IWrapper.FindBySample()
        {
            DataReaderIterator<TWrapper> iterator = FindBySample((TWrapper)this);
            return iterator.ToList().ToArray();
        }



    }
}
